package cn.edu.sdjzu.xg21.dao;

import cn.edu.sdjzu.xg21.domain.Department;
import cn.edu.sdjzu.xg21.domain.Teacher;
import cn.edu.sdjzu.xg21.exception.BysjException;
import cn.edu.sdjzu.xg21.service.DepartmentService;
import cn.edu.sdjzu.xg21.util.Condition;
import cn.edu.sdjzu.xg21.util.JdbcHelper;
import cn.edu.sdjzu.xg21.util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.TreeSet;

public class TeacherDao implements TeacherDaoInterface{
    // 将唯一的构造器（无参构造器）私有化，放置在本类外生成本类的对象
    private TeacherDao(){}
    private static TeacherDao teacherDao = new TeacherDao();
    private static Collection<Teacher> teachers;
    // 返回本类的唯一对象
    public static TeacherDao getInstance(){
        return teacherDao;
    }

    @Override
    public Teacher find(int id) throws SQLException, BysjException {
        // 创建School类型的引用，暂不创建对象
        Teacher desiredTeacher = null;
        // 获取数据库类型
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM teacher WHERE id=?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        // 如果表中存在id对应的元组，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()){
            String name = resultSet_select_id.getString("name");
            int department_id = resultSet_select_id.getInt("department");
            Department department = DepartmentService.getInstance().find(department_id);
            desiredTeacher = new Teacher(id,name,department);
        }
        // 关闭三个资源
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredTeacher;
    }

    @Override
    // 条件查询
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList) throws SQLException,BysjException {
        Collection<Teacher> desiredTeachers = new TreeSet<Teacher>();
        // 创建查询School的主语句
        StringBuilder sql_select = new StringBuilder("select * from teacher");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"Teacher");
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        // 执行预编译语句，返回结果集
        ResultSet resultSet = pstmt_select_all.executeQuery();
        // 遍历结果集
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int department_id = resultSet.getInt("department");
            Department department = DepartmentService.getInstance().find(department_id);
            // 以当前运足的各个字段值为参数，创建School对象
            Teacher desiredTeacher = new Teacher(id,name,department);
            // 将当前元组代表的School镀锡添加到desiredSchools集合中
            desiredTeachers.add(desiredTeacher);
        }
        JdbcHelper.close(resultSet,pstmt_select_all,connection);
        return desiredTeachers;
    }

    // 条件查询与分页查询
    @Override
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException, BysjException {
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
        // 创建查询School的主语句
        StringBuilder sql_select = new StringBuilder("select * from teacher");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        // 获取Teacher中的元组总数
        int totalNum = CommonDao.getInstance().count(connection,"Teacher",conditionList);
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        // 获取查询教师的语句对象
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        // 执行预编译语句，返回结果集
        ResultSet resultSet = pstmt_select_all.executeQuery();
        // 遍历结果集，根据每个元素创建对象，并添加到集合中
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int department_id = resultSet.getInt("department");
            Department department = DepartmentService.getInstance().find(department_id);
            // 以当前运足的各个字段值为参数，创建School对象
            Teacher desiredTeacher = new Teacher(id,name,department);
            // 将当前元组代表的School镀锡添加到desiredSchools集合中
            desiredTeachers.add(desiredTeacher);
        }
        JdbcHelper.close(resultSet,pstmt_select_all,connection);
        return desiredTeachers;
    }

//    @Override
//    // 添加一条教师信息,返回数据库为其自动生成的id值(考虑到后期可能添加User，故返回值为int类型的id值)
//    public int add(Teacher teacher, Connection connection) throws SQLException,BysjException{
//        // 根据链接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空
//        PreparedStatement pr = connection.prepareStatement("insert into teacher(name,department)" +
//                "values (?,?)", Statement.RETURN_GENERATED_KEYS); // 要求返回主键集
//        pr.setString(1,teacher.getName());
//        pr.setInt(2,teacher.getDepartment().getId());
//        // 执行预编译语句，用其返回值，影响的行数为affectedRowNum赋值
//        int rowAffectedNum = pr.executeUpdate();
//        int idOfAddedTeacher = 0;
//        ResultSet resultSet = pr.getGeneratedKeys(); // 获得生成的主键集合
//        if (resultSet.next()){
//            idOfAddedTeacher = resultSet.getInt(1); // 获得第一列的值（id）
//        }
//        resultSet.close();
//        pr.close();
//        // 本方法是事务的一部分，不能释放资源
//        // 新增元组的id
//        return idOfAddedTeacher;
//    }

    @Override
    // 考虑到可能要同时删除一个教师和一个用户，会视为同一事务，故要有一个参数为connection
    public boolean delete(int id, Connection connection) throws SQLException,BysjException{
        PreparedStatement pr = connection.prepareStatement("delete from teacher where id=?");
        pr.setInt(1,id);
        int affectedRow = pr.executeUpdate();
        pr.close();
        return affectedRow > 0;
    }

//    @Override
//    // 考虑到可能要同时更新一个教师和一个用户，会视为同一事务，故要有一个参数为connection
//    public boolean update(Teacher teacher,Connection connection) throws SQLException,BysjException{
//        // 准备预编译语句
//        String sql_update = "update teacher set name=?,department=? " +
//                "where id=?";
//        PreparedStatement pstmt_update = connection.prepareStatement(sql_update);
//        pstmt_update.setString(1,teacher.getName());
//        pstmt_update.setInt(2,teacher.getDepartment().getId());
//        pstmt_update.setInt(3,teacher.getId()); // 设置where=id,根据id找到唯一的元组
//        //执行预编译语句，影响的行数保存在rowAffected中
//        int rowAffected = pstmt_update.executeUpdate();
//        pstmt_update.close();
//        return rowAffected > 0;
//    }
}
